<?php


class ObservationdepthdetailTable extends Doctrine_Table
{
    
    public static function getInstance()
    {
        return Doctrine_Core::getTable('Observationdepthdetail');
    }

    /**
     *
     * @param string $dql
     * @param string $params
     */
    public function findDepthAndSpeciesDesc($dql, $params = array()) {
         /* select s.species, detail.quantity, detail.tag from observationdepthdetail detail
            inner join observationdepth depth on  depth.id = detail.observationdepthid
            inner join species s on s.id = detail.speciesid
            where depth.id = 1
            order by s.species
         */

        return   Doctrine_Query::create()
                ->select('s.species, detail.quantity, detail.tag')
                ->from('observationdepthdetail detail')
                ->leftJoin('detail.Observationdepth depth')
                ->leftJoin('detail.Species s')
                ->where($dql, $params)
                ->orderBy('s.species')
                ->fetchArray();
    }

    /**
     *
     * @param string $dql
     * @param string $params
     * @return string
     */
    public function getSpeciesDescriptionQuery($dql, $params = array()){
        return   Doctrine_Query::create()
                ->select('s.id, s.species, detail.id, detail.quantity, detail.tag, u.unit')
                ->from('observationdepthdetail detail')
                ->leftJoin('detail.Species s')
                ->leftJoin('detail.Unit u')
                ->where($dql, $params)
                ->orderBy('s.species');
    }

    /**
     * Extend from function findByDql but with limit parameter
     * @param string                    $dql Dql where clause
     * @param array $params             statement parameters
     * @param int $limit                limit
     * @param <type> $hydrationMode     @see
     * @return Doctrine_Collection or Array
     */
    public function findWithLimit($dql, $params = array(), $limit = null, $hydrationMode = null) {
        return $this->createQuery('od')
                ->where($dql, $params)
                ->limit($limit)
                ->execute();
    }

    /**
     * Insert or update collection of records into database
     * @param array $collection array of record
     * @return boolean if success return true, otherwise false
     */
    public function bulkInsertUpdate($collection){
        if (!$collection)
            return false;

        $records = new Doctrine_Collection($this->getTableName());
        $depthId;

        foreach($collection as $line) {

            if (is_null($line) || $line == "")
                continue;

            $record;

            // if id exists then it is editing, otherwise new species
            $record = $this->findOrNew(isset($line['id']) ? $line['id'] : "");

            // if depthid doesnt exist then it is new depth
            if (empty($line['depthid']))
            {
                if (empty($depthId))
                {
                    // create new observation depth record
                    $obsDepth = Doctrine::getTable('Observationdepth')
                                ->create(array(
                                    'observationid'     => $line['obsid'],
                                    'observationdepth'  => $line['depth']
                                    ));
                    $obsDepth->save();
                    $depthId = $obsDepth->id;
                }
                
                $line['depthid'] = $depthId;
            }

            $record->observationdepthid = $line['depthid'];
            $species = Doctrine::getTable('Species')->findOrCreate(array(
                'species' => $line['speciesname']
                ));
            $record->speciesid = $species->getId();
            $record->unitid = $line['unitid'];
            $record->quantity = $line['quantity'];
            $record->tag = $line['tags'];

            $records->add($record);
        }

        try {
            $records->save();

            ObjectunitTable::getInstance()->bulkInsertUpdate($records, Types::$OBJECT_TYPE_SPECIES);

            return true;
        } catch (Exception $e) {
            if (sfConfig::get('sf_logging_enabled'))
            {
              sfContext::getInstance()->getLogger()->debug($e->getMessage());
            }
            throw $e;
        }
    }

    /**
     * Find a record by $id, or if not found return new object
     * @param int $id
     * @return Observationdepthdetail
     */
    public function findOrNew($id)
    {
        if (!empty($id))
        {
            return $this->find(array($id));
        }
        else
        {
            $record = new Observationdepthdetail($this->getTableName(), true);
            $record->created_at = date(DATE_RFC822);
            return $record;
        }
    }

    /**
     * Find single tag that similar with $term
     * @param String $term
     * @return Array
     */
    public function getSimilarTags($term){
        $q = $this->createQuery('a')
                ->select('tag')
                ->where('tag like ?', '%'.$term.'%')
                ->distinct();
        $results = $q->fetchArray();

        

        $similars = array();
        foreach($results as $tags) {
            $singleTags = $this->extractSingleTag($term, $tags['tag']);
            $similars = array_merge($similars, $singleTags);
        }
        $similars = array_unique($similars);

//        sfContext::getInstance()->getLogger()->debug('similars: '. implode(',', $similars));
        
        return $similars;
    }

    /**
     * Since $tags can contains multiple values separated by comma,
     * then find only one tag that fits with $term.
     * Eg.
     * @param string $term
     * @param string $tags
     * @return string
     */
    function extractSingleTag($term, $tags) {
        $splits = explode(",", $tags);
        $singleTags = array();

        if ($splits && count($splits) > 0) {
            foreach ($splits as $part) {
                if (strpos(strtolower($part), strtolower($term)) > -1) {
                    $singleTags[] = trim($part);
                }
            }
        }
        
        return $singleTags;
    }


    /**
     * delete records
     * @param array $keys array of id
     * @return boolean 
     */
    public function delete($keys) {
        try {
            $to_remove_records = $this->findByDql('id IN (?' . str_repeat(',?', count($keys)-1) . ')', $keys);
            $to_remove_records->delete();
            return true;
        } catch (Exception $e) {
            return false;
        }
    }
}